package vn.edu.saigontech.ncms.service.impl;

import java.util.Collection;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Restrictions;

import vn.edu.saigontech.ncms.model.info.ReceiptMaintenance;
import vn.edu.saigontech.ncms.service.ReceiptMaintenanceService;

@SuppressWarnings("unchecked")
public class ReceiptMaintenanceServiceImpl extends HibernateGenericService<ReceiptMaintenance, Long> implements ReceiptMaintenanceService {

	@Override
	public ReceiptMaintenance findByReceiptId(Long receiptId) {
		Criteria criteria = this.createCriteria();
		criteria.createCriteria("receipt").add(Restrictions.eq("id", receiptId));
		return (ReceiptMaintenance) criteria.uniqueResult();
	}

	@Override
	public Collection<ReceiptMaintenance> findByStudentId(Long studentId) {
		Criteria criteria = this.createCriteria();
		Criteria receiptCriteria = criteria.createCriteria("receipt");
		receiptCriteria.createCriteria("student").add(Restrictions.eq("id", studentId));
		return criteria.list();
	}

	@Override
	public Collection<ReceiptMaintenance> findActiveByStudentId(Long studentId) {
		Criteria criteria = this.createCriteria();
		criteria.add(Restrictions.eq("active", true));
		Criteria receiptCriteria = criteria.createCriteria("receipt");
		receiptCriteria.createCriteria("student").add(Restrictions.eq("id", studentId));
		return criteria.list();
	}

	@Override
	public List<Object[]> findByMonthAndYear(int month, int year) {
		String sql = "SELECT rm.date, " +
				"st.last_name, st.first_name, st.date_of_birth, st.phone_number, " +
				"offer.name, re.number as renumber, " +
				"rm.number, rm.fee, " +
				"rm.reason, " +
				"rm.id " +
				"FROM ncms_receipt AS re, ncms_receipt_maintenance AS rm, ncms_student AS st, " +
				"ncms_offering AS offer " +
				"WHERE extract(month from rm.date)=? AND extract(year from rm.date)=? " +
				"AND re.offering_id = offer.id " +
				"AND re.student_id = st.id " +
				"AND rm.receipt_id = re.id " +
				"ORDER BY substring(rm.number from '...$') ASC";
		SQLQuery query = getSession().createSQLQuery(sql);
		
		query = query.addScalar("date", Hibernate.DATE);
		query = query.addScalar("last_name", Hibernate.STRING);
		query = query.addScalar("first_name", Hibernate.STRING);
		query = query.addScalar("date_of_birth", Hibernate.DATE);
		query = query.addScalar("phone_number", Hibernate.STRING);
		query = query.addScalar("renumber", Hibernate.STRING);
		query = query.addScalar("name", Hibernate.STRING);
		query = query.addScalar("number", Hibernate.STRING);
		query = query.addScalar("fee", Hibernate.DOUBLE);
		query = query.addScalar("reason", Hibernate.STRING);
		query = query.addScalar("id", Hibernate.LONG);
		
		return query.setInteger(0, month).setInteger(1, year).list();
	}

	@Override
	public List<Object[]> findByDates(Date fromDate, Date toDate) {
		String sql = "SELECT rm.date, " +
				"st.last_name, st.first_name, st.date_of_birth, st.phone_number, " +
				"offer.name, re.number as renumber, " +
				"rm.number, rm.fee, " +
				"rm.reason, " +
				"rm.id " +
				"FROM ncms_receipt AS re, ncms_receipt_maintenance AS rm, ncms_student AS st, " +
				"ncms_offering AS offer " +
				"WHERE rm.date BETWEEN ? AND ? " +
				"AND re.offering_id = offer.id " +
				"AND re.student_id = st.id " +
				"AND rm.receipt_id = re.id " +
				"ORDER BY substring(rm.number from '...$') ASC";
		SQLQuery query = getSession().createSQLQuery(sql);
		
		query = query.addScalar("date", Hibernate.DATE);
		query = query.addScalar("last_name", Hibernate.STRING);
		query = query.addScalar("first_name", Hibernate.STRING);
		query = query.addScalar("date_of_birth", Hibernate.DATE);
		query = query.addScalar("phone_number", Hibernate.STRING);
		query = query.addScalar("name", Hibernate.STRING);
		query = query.addScalar("renumber", Hibernate.STRING);
		query = query.addScalar("number", Hibernate.STRING);
		query = query.addScalar("fee", Hibernate.DOUBLE);
		query = query.addScalar("reason", Hibernate.STRING);
		query = query.addScalar("id", Hibernate.LONG);
		
		return query.setDate(0, fromDate).setDate(1, toDate).list();
	}
}